﻿using DLLmySQL;
using jmit.Utils;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OJ;
using SqlserverHelper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

//查询竞品分析
public partial class getDataJPFX : System.Web.UI.Page
{
    public string strCnn = ConfigurationSettings.AppSettings["DataBaseString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        string shop_code = "";
        string day_begin = "";
        string day_end = "";
        string shop_attr_name = "";
        string zx_status = "";
        string yb_status = "";
        string is_export = "false";
        string user_identity = "";

        //day_begin = "2025-08-13";
        //day_end = "2025-08-19";
        //user_identity = "25";

        if (Request.Form["shop_code"] != null && Request.Form["shop_code"].ToString().Trim() != "")
        {
            shop_code = Request.Form["shop_code"].ToString();
        }
        if (Request.Form["day_begin"] != null && Request.Form["day_begin"].ToString().Trim() != "")
        {
            day_begin = Request.Form["day_begin"].ToString();
        }
        if (Request.Form["day_end"] != null && Request.Form["day_end"].ToString().Trim() != "")
        {
            day_end = Request.Form["day_end"].ToString();
        }
        if (Request.Form["shop_attr_name"] != null && Request.Form["shop_attr_name"].ToString().Trim() != "")
        {
            shop_attr_name = Request.Form["shop_attr_name"].ToString();
        }
        if (Request.Form["zx_status"] != null && Request.Form["zx_status"].ToString().Trim() != "")
        {
            zx_status = Request.Form["zx_status"].ToString();
        }
        if (Request.Form["yb_status"] != null && Request.Form["yb_status"].ToString().Trim() != "")
        {
            yb_status = Request.Form["yb_status"].ToString();
        }
        if (Request.Form["is_export"] != null && Request.Form["is_export"].ToString().Trim() != "")
        {
            is_export = Request.Form["is_export"].ToString();
        }
        if (Request.Form["user_identity"] != null && Request.Form["user_identity"].ToString().Trim() != "")
        {
            user_identity = Request.Form["user_identity"].ToString();
        }
        string stResult = f_getDataJPFX(shop_code, day_begin, day_end, shop_attr_name, zx_status, yb_status, is_export, user_identity);
        Response.Write(stResult);
    }

    public string f_getDataJPFX(string shop_code, string day_begin, string day_end, string shop_attr_name, string zx_status, string yb_status, string is_export,string user_identity)
    {

        Define.retJPFX ret = new Define.retJPFX();
        List<Define.JPFX> L = new List<Define.JPFX>();
        List<Define.lineChartData> L1 = new List<Define.lineChartData>();
        List<Define.lineChartData> L12 = new List<Define.lineChartData>();
        List<Define.pieChartData> L2 = new List<Define.pieChartData>();
        List<Define.barChartData> B1 = new List<Define.barChartData>();

        /*LogBLL.log("shop_code:" + shop_code);
        LogBLL.log("day_begin:" + day_begin);
        LogBLL.log("day_end:" + day_end);
        LogBLL.log("shop_attr_name:" + shop_attr_name);
        LogBLL.log("zx_status:" + zx_status);
        LogBLL.log("yb_status:" + yb_status);
        LogBLL.log("is_export:" + is_export);
        LogBLL.log("user_identity:" + user_identity);*/
        try
        {
            string s = "";
            string s1 = "";
            if (shop_code != "")
            {
                s = s + " and dept_name = '" + shop_code.Trim() + "'";
                s1 = s1 + " and dept_name = '" + shop_code.Trim() + "'";
            }
            //日期
            if (day_begin == "" && day_end == "")
            {
                s = s + " and ymd BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW()";
                s1 = s1 + " and ymd BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW()"; // 后加的
            }
            if (day_begin != "")
            {
                //s = s + " and DATE_FORMAT(ymd,'%Y-%m-%d') >= '" + day_begin.Trim() + "'";
                //s1 = s1 + " and DATE_FORMAT(ymd,'%Y-%m-%d') >= '" + day_begin.Trim() + "'"; // 后加的
                s = s + " and ymd >= '" + day_begin.Trim() + " 00:00:00'";
                s1 = s1 + " and ymd >= '" + day_begin.Trim() + " 00:00:00'";

            }
            if (day_end != "")
            {
                //s = s + " and DATE_FORMAT(ymd,'%Y-%m-%d') <= '" + day_end.Trim() + "'";
                //s1 = s1 + " and DATE_FORMAT(ymd,'%Y-%m-%d') <= '" + day_end.Trim() + "'"; // 后加的
                s = s + " and ymd <= '" + day_end.Trim() + " 23:59:59'";
                s1 = s1 + " and ymd <= '" + day_end.Trim() + " 23:59:59'";
            }
            //店铺属性
            if (shop_attr_name == "院边店")
            {
                s = s + " and shop_attr in(100,101,110,111)";
                s1 = s1 + " and shop_attr in(100,101,110,111)";
            }
            if (shop_attr_name == "社区店")
            {
                s = s + " and shop_attr in(010,011,110,111)";
                s1 = s1 + " and shop_attr in(100,101,110,111)";
            }
            if (shop_attr_name == "商业店")
            {
                s = s + " and shop_attr in(001,101,011,111)";
                s1 = s1 + " and shop_attr in(100,101,110,111)";
            }    
            //线上线下
            if (zx_status == "线上")
            {
                s = s + " and pay_type in(219,209,216)"; //美团，饿百，微商城
                s1 = s1 + " and pay_type in(219,209,216)";
            }
            if (zx_status == "线下")
            {
                s = s + " and pay_type not in(219,209,216)"; //美团，饿百，微商城
                s1 = s1 + " and pay_type not in(219,209,216)";
            }
            //医保/非医保
            if (yb_status == "医保")
            {
                s = s + " and dept_name LIKE '%医保%'";
                s1 = s1 + " and dept_name LIKE '%医保%'";
            }
            if (yb_status == "非医保")
            {
                s = s + " and dept_name NOT LIKE '%医保%'";
                s1 = s1 + " and dept_name NOT LIKE '%医保%'";
            }

            // user_identity
            if (user_identity != "")
            {
                //strFrom =  " and dsh_prd.user_identity = '"+ user_identity + "' ";
                // s1 = s1 + " and dept_name NOT LIKE '%医保%'";
            }

            // 获取竞品的分类
            string view_dsnfxfhfl = " select artiid from dsnclfl where clfl in (select clfl from dsnclfl where artiid in (select artiid from dsnsupply where supply_type in (SELECT SUBSTRING_INDEX(from_name, ' ', 1) AS supple_type FROM dsn_from WHERE from_id = '"+ user_identity + "') )) and artiid not in (select artiid from dsnsupply where supply_type in (SELECT SUBSTRING_INDEX(from_name, ' ', 1) AS supple_type FROM dsn_from WHERE from_id = '"+ user_identity + "') )  ";
            LogBLL.log("【竞品分析】开始");

            //data
            /*string strQuery = "SELECT a.ymd,a.dept_name,a.sell_qty,a.sell_price,IFNULL(b.class_qty,0) class_qty,IFNULL(b.class_amt,0) class_amt FROM "
                    + " (SELECT DATE_FORMAT(ymd,'%Y-%m-%d') ymd,dept_name,SUM(sell_qty) sell_qty,SUM(sell_price)  sell_price FROM dsnfxcs  "
                    + " WHERE 1=1 " + s + "  GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d'),dept_name) a  "
                    + " LEFT JOIN "
                    + "(SELECT DATE_FORMAT(ymd,'%Y-%m-%d') ymd,dept_name,SUM(sell_qty) class_qty,SUM(sell_price)  class_amt FROM dsnfxcs "
                    + " WHERE 1=1 " + s + " AND class_id IN(10025,15948,16474,16526,17630,1773,17750,18496,4630) GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d'),dept_name) b "
                    + " ON a.ymd = b.ymd AND a.dept_name = b.dept_name ";*/

            // 导出的数据（）
            string strQuery = String.Format(@"
SELECT
	a.ymd,
	a.dept_name,
	a.sell_qty,
	a.sell_price,
	IFNULL( b.class_qty, 0 ) class_qty,
	IFNULL( b.class_amt, 0 ) class_amt 
FROM
	(   SELECT DATE_FORMAT( ymd, '%Y-%m-%d' ) ymd, dept_name, SUM( sell_qty ) sell_qty, SUM( sell_price ) sell_price 
	    FROM dsnfxcs 
		INNER JOIN dsh_prd on dsh_prd.class_id = dsnfxcs.class_id
		WHERE 1 = 1 
        and dsh_prd.user_identity = '{0}'
            {1}
		GROUP BY DATE_FORMAT( ymd, '%Y-%m-%d' ), dept_name 
    ) a
	LEFT JOIN (
	SELECT
		DATE_FORMAT( ymd, '%Y-%m-%d' ) ymd,
		dept_name,
		SUM( sell_qty ) class_qty,
		SUM( sell_price ) class_amt 
	FROM
		dsnfxcs 
	WHERE
		1 = 1 
        {1}
		AND class_id IN ( {2}) 
	GROUP BY
		DATE_FORMAT( ymd, '%Y-%m-%d' ),
		dept_name 
	) b ON a.ymd = b.ymd 
	AND a.dept_name = b.dept_name", user_identity, s, view_dsnfxfhfl);

            // 导出数据
            //string strQuery = "";



            //lineChartData  近七日客单价  and DATE_FORMAT(ymd,'%Y-%m-%d') >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) 
            string strQuery1 = String.Format(@"

            select DATE_FORMAT(a.ymd,'%Y-%m-%d'),ROUND(SUM(a.sell_price) / COUNT(*), 2) AS kdj 
            from (
                select distinct ymd, order_id,prd_name,sell_price from dsnfxcs 
                where 1=1 
                
                {0}
            ) a 
            where 1=1 
            and a.order_id IN (
	            select distinct order_id from dsnfxcs where 1=1 
	            and class_id in (select class_id from dsnfxfhfl )
	            {0}
	        )
	        GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d')
            ", s1);
            //LogBLL.log(strQuery1);
            //LogBLL.log("【竞品分析】【近七日客单价】" + strQuery1);
            //lineChartData  近七日数量
            string strQuery12 = String.Format(@"

            select t1.ymd,sum(sell_qty) as qty from (
            select distinct DATE_FORMAT(ymd,'%Y-%m-%d') as ymd , order_id,prd_name,sell_qty from dsnfxcs where 1=1 
            and class_id in (select class_id from dsnfxfhfl )
            and DATE_FORMAT(ymd,'%Y-%m-%d') >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
            {0}
            ) t1
            GROUP BY t1.ymd
            ", s1);


            //pieChartData
            string strQuery2 = "SELECT IFNULL(SUM(sell_price),0) class_amt FROM dsnfxcs WHERE class_id IN (select class_id from dsnfxfhfl ) " + s; //大类金额
            string strQuery3 = "SELECT IFNULL(SUM(sell_price),0) sell_price FROM dsnfxcs where class_id NOT IN (select class_id from dsnfxfhfl ) " + s;//其他金额

            //data
            string outputFile = "";
            string outputUrl = "https://" + Request.Url.Authority;
            if (is_export == "true")
            {
                OperLog.log("竞品分析-导出", user_identity);

                DataTable DT = CmySQL.GetDataTbale(strCnn, strQuery);
                if (DT != null && DT.Rows.Count > 0)
                {
                    /*for (int i = 0; i < DT.Rows.Count; i++)
                    {
                        Define.JPFX r = new Define.JPFX();
                        r.ymd = DT.Rows[i][0].ToString().Trim();
                        r.dept_name = DT.Rows[i][1].ToString().Trim();
                        r.sell_qty = DT.Rows[i][2].ToString().Trim();
                        r.sell_price = DT.Rows[i][3].ToString().Trim();
                        r.class_qty = DT.Rows[i][4].ToString().Trim();
                        r.class_amt = DT.Rows[i][5].ToString().Trim();
                        L.Add(r);


                    }*/
                }

                string fileName = "out_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";
                outputFile = AppDomain.CurrentDomain.BaseDirectory + "out\\" + fileName;
                outputUrl = outputUrl + "/api_sst/out" + "/" + fileName;
                ExportToCsv(DT, outputFile);
            }
            else {
                outputUrl = "";
                OperLog.log("竞品分析-查询", user_identity);

                //lineChartData
                LogBLL.log("【竞品分析】【近七日客单价】" + strQuery1);
                DataTable DT1 = CmySQL.GetDataTbale(strCnn, strQuery1);
                if (DT1 != null && DT1.Rows.Count > 0)
                {
                    for (int i = 0; i < DT1.Rows.Count; i++)
                    {
                        Define.lineChartData r1 = new Define.lineChartData();
                        r1.name = DT1.Rows[i][0].ToString().Trim();
                        r1.data = DT1.Rows[i][1].ToString().Trim();
                        L1.Add(r1);
                    }
                }

                LogBLL.log("【竞品分析】【近七日数量】" + strQuery12);
                DataTable DT12 = CmySQL.GetDataTbale(strCnn, strQuery12);
                if (DT12 != null && DT12.Rows.Count > 0)
                {
                    for (int i = 0; i < DT12.Rows.Count; i++)
                    {
                        Define.lineChartData r1 = new Define.lineChartData();
                        r1.name = DT12.Rows[i][0].ToString().Trim();
                        r1.data = DT12.Rows[i][1].ToString().Trim();
                        L12.Add(r1);
                    }
                }


                //pieChartData
                LogBLL.log("【竞品分析】【pieChartData大类】" + strQuery2);
                LogBLL.log("【竞品分析】【pieChartData其他】" + strQuery3);
                //DataTable DT2 = CmySQL.GetDataTbale(strCnn, strQuery2);
                //DataTable DT3 = CmySQL.GetDataTbale(strCnn, strQuery3);
                //Define.pieChartData p1 = new Define.pieChartData() { name = "大类", data = DT2.Rows[0][0] };
                //Define.pieChartData p2 = new Define.pieChartData() { name = "其他", data = DT3.Rows[0][0] };
                //L2.Add(p1);
                //L2.Add(p2);

                LogBLL.log("【竞品分析】【本品数据排名】开始");
                // add 
                string strSQL = "";

                // 本品数据
                strSQL = "select dsh_prd.prd_name,'0' flag,SUM( sell_qty ) sell_qty,sum(sell_price) amt " +
                         " from dsnfxcs  " +
                         " INNER JOIN dsh_prd on dsh_prd.class_id = dsnfxcs.class_id " +
                         " WHERE 1 = 1  " +
                         " and dsh_prd.user_identity = '" + user_identity + "'  " + s +
                         " group by dsh_prd.prd_name  " +

                         " UNION " +
                         // 竞品数据
                         " select  '' as prd_name,'1' flag,SUM( sell_qty ) sell_qty,sum(sell_price) amt " +
                         " from dsnfxcs  " +
                         " WHERE 1 = 1  " +
                         //" AND class_id IN ( select class_id from dsnfxfhfl )  " + s +
                         " AND class_id IN ( " + view_dsnfxfhfl + " )  " + s +
                         " group by prd_name " +
                         " order by amt desc ";

                LogBLL.log("【竞品分析】【本品数据排名】" + strSQL);
                DataTable DT_bar = CmySQL.GetDataTbale(strCnn, strSQL);
                if (DT_bar != null && DT_bar.Rows.Count > 0)
                {
                    for (int i = 0; i < DT_bar.Rows.Count; i++)
                    {
                        Define.barChartData r1 = new Define.barChartData();
                        r1.name = DT_bar.Rows[i][0].ToString().Trim();
                        r1.flag = DT_bar.Rows[i][1].ToString().Trim();
                        r1.data2 = DT_bar.Rows[i][2].ToString().Trim();
                        r1.data = DT_bar.Rows[i][3].ToString().Trim();
                        B1.Add(r1);
                    }
                }

            }

            


            ret.code = 200;
            ret.msg = "success";

            ret.data = L;
            ret.lineChartData = L1;
            ret.lineChartData2 = L12;
            ret.barChartData = B1;
            ret.pieChartData = L2;
            ret.outputUrl = outputUrl;
        }
        catch (Exception e)
        {
            string er = e.Message;
            ret.code = 20000;
            ret.msg = er;
            ret.data = null;
            ret.lineChartData = null;
            ret.pieChartData = null;
        }

        return Ojson.ObjectToJson(ret);
    }

    /// <summary>
    /// DataTable导出成CSV文件
    /// </summary>
    /// <param name="dataTable">数据集</param>
    /// <param name="filePath">导出文件地址</param>
    public void ExportToCsv(DataTable dataTable, string filePath)
    {
        StringBuilder sb = new StringBuilder();
        /*foreach (DataColumn col in dataTable.Columns)
        {
            sb.Append(col.ColumnName + ",");
        }*/
        //sb.Append("进货");
        sb.Append("日期,店铺,数量,金额,大类数量,大类金额");

        sb.AppendLine();
        foreach (DataRow row in dataTable.Rows)
        {
            foreach (DataColumn col in dataTable.Columns)
            {
                sb.Append(row[col].ToString().Replace(",", " ") + ",");
            }
            sb.AppendLine();
        }

        using (StreamWriter writer = new StreamWriter(filePath))
        {
            writer.Write(sb.ToString());
        }
    }
}